<?php
require_once 'config.php';

// 检查用户是否登录
session_start();
if (!isset($_SESSION['user_id'])) {
    die('请先登录');
}

$userId = $_SESSION['user_id'];

try {
    $db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8", DB_USER, DB_PASS);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h2>书签数量统计调试工具</h2>";
    echo "<style>
        body { font-family: Arial, sans-serif; padding: 20px; }
        table { border-collapse: collapse; width: 100%; margin-top: 20px; }
        th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
        th { background-color: #667eea; color: white; }
        tr:nth-child(even) { background-color: #f2f2f2; }
        .warning { color: red; font-weight: bold; }
        .success { color: green; font-weight: bold; }
        .info { background-color: #e3f2fd; padding: 15px; margin: 20px 0; border-left: 4px solid #2196F3; }
    </style>";
    
    // 1. 获取所有分组及其统计的书签数量
    $categorySql = "
        SELECT c.id, c.name, c.color, c.icon, 
               COUNT(b.id) as counted_bookmarks
        FROM categories c
        LEFT JOIN bookmarks b ON c.id = b.category_id AND b.user_id = c.user_id
        WHERE c.user_id = ?
        GROUP BY c.id, c.name, c.color, c.icon
        ORDER BY c.sort_order ASC
    ";
    
    $stmt = $db->prepare($categorySql);
    $stmt->execute([$userId]);
    $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // 2. 获取实际书签总数
    $totalBookmarksSql = "SELECT COUNT(*) as total FROM bookmarks WHERE user_id = ?";
    $stmt = $db->prepare($totalBookmarksSql);
    $stmt->execute([$userId]);
    $totalBookmarks = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // 3. 获取未分组的书签数量
    $uncategorizedSql = "SELECT COUNT(*) as total FROM bookmarks WHERE user_id = ? AND (category_id IS NULL OR category_id = 0)";
    $stmt = $db->prepare($uncategorizedSql);
    $stmt->execute([$userId]);
    $uncategorizedCount = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // 4. 统计所有分组的书签总和
    $categorySum = array_sum(array_column($categories, 'counted_bookmarks'));
    
    echo "<div class='info'>";
    echo "<h3>总体统计</h3>";
    echo "<p><strong>用户ID:</strong> {$userId}</p>";
    echo "<p><strong>书签总数:</strong> {$totalBookmarks}</p>";
    echo "<p><strong>未分组书签:</strong> {$uncategorizedCount}</p>";
    echo "<p><strong>所有分组书签总和:</strong> {$categorySum}</p>";
    
    $diff = $totalBookmarks - $categorySum - $uncategorizedCount;
    if ($diff != 0) {
        echo "<p class='warning'>⚠️ 数据不一致！差值: {$diff}</p>";
        echo "<p class='warning'>这表示有些书签的 category_id 指向了不存在的分组！</p>";
    } else {
        echo "<p class='success'>✓ 数据一致</p>";
    }
    echo "</div>";
    
    // 5. 显示每个分组的详细信息
    echo "<h3>分组详细统计</h3>";
    echo "<table>";
    echo "<tr>";
    echo "<th>分组ID</th>";
    echo "<th>分组名称</th>";
    echo "<th>统计书签数</th>";
    echo "<th>实际书签列表</th>";
    echo "<th>操作</th>";
    echo "</tr>";
    
    foreach ($categories as $category) {
        // 获取该分组下的所有书签ID
        $bookmarksSql = "SELECT id, name FROM bookmarks WHERE user_id = ? AND category_id = ? LIMIT 10";
        $stmt = $db->prepare($bookmarksSql);
        $stmt->execute([$userId, $category['id']]);
        $bookmarksList = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $bookmarksPreview = count($bookmarksList) > 0 
            ? implode(', ', array_map(function($b) { return $b['name']; }, $bookmarksList))
            : '无';
        
        if (count($bookmarksList) >= 10) {
            $bookmarksPreview .= '...';
        }
        
        echo "<tr>";
        echo "<td>{$category['id']}</td>";
        echo "<td>{$category['name']}</td>";
        echo "<td><strong>{$category['counted_bookmarks']}</strong></td>";
        echo "<td>{$bookmarksPreview}</td>";
        echo "<td><a href='?action=view&category_id={$category['id']}' target='_blank'>查看全部</a></td>";
        echo "</tr>";
    }
    
    echo "</table>";
    
    // 6. 检查孤立的书签（category_id 指向不存在的分组）
    echo "<h3>孤立书签检查（指向不存在的分组）</h3>";
    $orphanSql = "
        SELECT b.id, b.name, b.category_id
        FROM bookmarks b
        LEFT JOIN categories c ON b.category_id = c.id
        WHERE b.user_id = ? AND b.category_id IS NOT NULL AND c.id IS NULL
    ";
    $stmt = $db->prepare($orphanSql);
    $stmt->execute([$userId]);
    $orphanBookmarks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($orphanBookmarks) > 0) {
        echo "<p class='warning'>发现 " . count($orphanBookmarks) . " 个孤立书签：</p>";
        echo "<table>";
        echo "<tr><th>书签ID</th><th>书签名称</th><th>指向的分组ID</th></tr>";
        foreach ($orphanBookmarks as $bookmark) {
            echo "<tr>";
            echo "<td>{$bookmark['id']}</td>";
            echo "<td>{$bookmark['name']}</td>";
            echo "<td>{$bookmark['category_id']} (不存在)</td>";
            echo "</tr>";
        }
        echo "</table>";
        echo "<p><a href='?action=fix_orphan' style='color: red; font-weight: bold;'>🔧 修复孤立书签（将它们移到未分组）</a></p>";
    } else {
        echo "<p class='success'>✓ 没有孤立书签</p>";
    }
    
    // 处理修复操作
    if (isset($_GET['action'])) {
        if ($_GET['action'] === 'fix_orphan') {
            $fixSql = "UPDATE bookmarks SET category_id = NULL WHERE user_id = ? AND category_id NOT IN (SELECT id FROM categories WHERE user_id = ?)";
            $stmt = $db->prepare($fixSql);
            $stmt->execute([$userId, $userId]);
            $affected = $stmt->rowCount();
            echo "<div class='info'>";
            echo "<p class='success'>✓ 已修复 {$affected} 个孤立书签</p>";
            echo "<p><a href='debug-count.php'>刷新页面查看结果</a></p>";
            echo "</div>";
        } elseif ($_GET['action'] === 'view' && isset($_GET['category_id'])) {
            $categoryId = $_GET['category_id'];
            $viewSql = "SELECT id, name, url FROM bookmarks WHERE user_id = ? AND category_id = ?";
            $stmt = $db->prepare($viewSql);
            $stmt->execute([$userId, $categoryId]);
            $bookmarks = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            echo "<h3>分组 {$categoryId} 的所有书签 (共 " . count($bookmarks) . " 个)</h3>";
            echo "<table>";
            echo "<tr><th>ID</th><th>名称</th><th>URL</th></tr>";
            foreach ($bookmarks as $b) {
                echo "<tr><td>{$b['id']}</td><td>{$b['name']}</td><td>{$b['url']}</td></tr>";
            }
            echo "</table>";
        }
    }
    
} catch (PDOException $e) {
    echo "<p class='warning'>数据库错误: " . $e->getMessage() . "</p>";
}
?>

